from django.db import connection
from django.core.cache import cache
from django.db.models import Count, Avg, Max
import json
import zlib
import logging
from functools import lru_cache
from typing import List, Dict, Any
logger = logging.getLogger(__name__)
class WorldMapDataService:
    """世界地图数据服务类"""
    # 缓存配置
    CACHE_KEY = 'worldmap_data_v2'
    CACHE_TIMEOUT = 3600 # 1小时
    # 国家名称映射表
    COUNTRY_MAPPING = {
        # 中国相关
        '中国': '中国大陆',
        '中国大陆': '中国大陆',
        '香港': '中国大陆',
        '中国香港': '中国大陆',
        '台湾': '中国大陆',
        '中国台湾': '中国大陆',
        # 美国相关
        'USA': '美国',
        'United States': '美国',
        'America': '美国',
        # 英国相关
        'UK': '英国',
        'United Kingdom': '英国',
        'England': '英国',
        # 亚洲国家
        'Japan': '日本',
        'Korea': '韩国',
        'South Korea': '韩国',
        'Thailand': '泰国',
        'Singapore': '新加坡',
        'India': '印度',
        'Iran': '伊朗',
        # 欧洲国家
        'France': '法国',
        'Germany': '德国',
        'Italy': '意大利',
        'Spain': '西班牙',
        'Russia': '俄罗斯',
        'Sweden': '瑞典',
        'Denmark': '丹麦',
        'Norway': '挪威',
        'Finland': '芬兰',
        'Netherlands': '荷兰',
        'Belgium': '比利时',
        'Switzerland': '瑞士',
        'Austria': '奥地利',
        'Ireland': '爱尔兰',
        'Greece': '希腊',
        'Poland': '波兰',
        'Hungary': '匈牙利',
        'Czech': '捷克',
        # 美洲国家
        'Canada': '加拿大',
        'Brazil': '巴西',
        'Mexico': '墨西哥',
        'Argentina': '阿根廷',
        'Chile': '智利',
        # 大洋洲国家
        'Australia': '澳大利亚',
        'New Zealand': '新西兰',
        # 非洲国家
        'South Africa': '南非',
        # 中东国家
        'Lebanon': '黎巴嫩',
        'Jordan': '约旦',
        'Cyprus': '塞浦路斯',
        'Qatar': '卡塔尔'
    }
    @classmethod
    def get_worldmap_data(cls) -> Dict[str, Any]:
        """获取世界地图数据的主方法"""
        try:
            # 1. 尝试从缓存获取
            cached_data = cache.get(cls.CACHE_KEY)
            if cached_data:
                return json.loads(zlib.decompress(cached_data))
            # 2. 从数据库获取数据
            data = cls._fetch_data_from_db()
            # 3. 压缩并缓存数据
            compressed_data = zlib.compress(json.dumps(data).encode())
            cache.set(cls.CACHE_KEY, compressed_data, cls.CACHE_TIMEOUT)
            return data
        except Exception as e:
            logger.error(f"获取世界地图数据失败: {str(e)}")
            return []

    @classmethod
    def _fetch_data_from_db(cls) -> List[Dict[str, Any]]:
        """从数据库获取数据"""
        try:
            with connection.cursor() as cursor:
                # 使用优化的SQL查询
                cursor.execute("""
                    WITH movie_countries AS (
                    -- 将电影按国家拆分，每个国家一行
                    SELECT
                        m.id,
                        m.title,
                        m.rating,
                        m.year,
                        TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(
                            REPLACE(REPLACE(m.country, ' ', '/'), '//', '/'),'/', n.n), '/', -1)) as country
                    FROM spider_movie m
                    CROSS JOIN (
                        SELECT 1 as n UNION ALL SELECT 2 UNION ALL SELECT 3
                        UNION ALL
                        SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION
                        ALL
                        SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION
                        ALL SELECT 10
                    ) n
                    WHERE
                        m.country IS NOT NULL
                        AND m.country != ''
                        AND n.n <= 1 + (LENGTH(REPLACE(REPLACE(m.country, '', '/'), '//', '/')) -LENGTH(REPLACE(REPLACE(REPLACE(m.country,' ', '/'), '//', '/'), '/', '')))
                        ),
                        country_stats AS (
                            -- 计算每个国家的统计数据
                                SELECT
                                    country,
                                    COUNT(*) as count,
                                    AVG(rating) as avg_rating,
                                    MAX(rating) as max_rating
                                FROM movie_countries
                                WHERE country != ''
                                GROUP BY country
                                ),
                        top_movies AS (
                            -- 获取每个国家评分最高的电影
                            SELECT
                                mc1.country,
                                mc1.title,
                                mc1.rating,
                                mc1.year
                            FROM movie_countries mc1
                            INNER JOIN (
                                SELECT country, MAX(rating) as max_rating
                                FROM movie_countries
                                GROUP BY country
                            ) mc2 ON mc1.country = mc2.country AND mc1.rating =mc2.max_rating
                        )
                        SELECT
                            cs.country,
                            cs.count,
                            ROUND(cs.avg_rating, 1) as avg_rating,
                            tm.title as top_movie_title,
                            tm.rating as top_movie_rating,
                            tm.year as top_movie_year
                        FROM country_stats cs
                        LEFT JOIN top_movies tm ON cs.country = tm.country
                        ORDER BY cs.count DESC
                        """)
                
                # 处理查询结果
                result = []
                for row in cursor.fetchall():
                    country = cls._normalize_country_name(row[0])
                    if not country: # 跳过无效的国家名
                        continue
                    result.append({
                        'country': country,
                        'count': row[1],
                        'avg_rating': float(row[2]) if row[2] else 0,
                        'top_movies': [{
                            'title': row[3] or '未知',
                            'rating': float(row[4]) if row[4] else 0,
                            'year': row[5] or '未知'
                        }] if row[3] else []
                    })
                return result
        except Exception as e:
            logger.error(f"数据库查询失败: {str(e)}")
            return []
    @staticmethod
    @lru_cache(maxsize=128)
    def _normalize_country_name(country: str) -> str:
        """标准化国家名称（使用LRU缓存优化）"""
        if not country:
            return None
        # 处理包含多个国家的情况
        # 1. 先按空格分割
        countries = country.split()
        # 2. 对每个部分再按斜杠分割
        all_countries = []
        for c in countries:
            all_countries.extend(c.split('/'))
        # 3. 清理每个国家名称
        cleaned_countries = []
        for c in all_countries:
            c = c.strip()
            # 处理括号中的额外信息
            if '(' in c:
                c = c.split('(')[0].strip()
            if c: # 只添加非空的国家名
                cleaned_countries.append(c)
        # 4. 如果只有一个国家，直接返回映射结果
        if len(cleaned_countries) == 1:
            return WorldMapDataService.COUNTRY_MAPPING.get(cleaned_countries[0],cleaned_countries[0])
        # 5. 如果有多个国家，返回第一个有效的国家
        for c in cleaned_countries:
            normalized = WorldMapDataService.COUNTRY_MAPPING.get(c, c)
            if normalized: # 如果映射结果有效
                return normalized
        return None
    @classmethod
    def clear_cache(cls):
        """清除缓存"""
        cache.delete(cls.CACHE_KEY)
